lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/建表语句.sql

IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_ErpShopEvententry]') AND type in (N'U'))
begin
CREATE TABLE [dbo].[tb_ErpShopEvententry](

	[ID]  [int] identity(1,1) primary key not null, 	--ID主建
	[Matter_CreateName]   		[varchar](16) not null,	 --申请人
	[Matter_CreateDatetime]  	[Datetime] not null, 	 --申请时间
	[Matter_content]   		[varchar](200)not null,   --申请内容
	[Matter_state]			[varchar](10)not null,    --申请状态
	[Matter_UpdateName]		[varchar](16)not null,   --审批人	 
	[Matter_UpdateDatetime]   	[Datetime],      	 --审核时间
	[Matter_opinion]		[varchar](200)	 	--审批人意见
                    )

EXEC sys.sp_addextendedproperty @name=N'Evententry', @value=N'ID主建' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_Evententry', 
@level2type=N'COLUMN',@level2name=N'ID'

EXEC sys.sp_addextendedproperty @name=N'Evententry', @value=N'申请人' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_Evententry', 
@level2type=N'COLUMN',@level2name=N'Matter_CreateName'

EXEC sys.sp_addextendedproperty @name=N'Evententry', @value=N'申请时间' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_Evententry', 
@level2type=N'COLUMN',@level2name=N'Matter_CreateDatetime'

EXEC sys.sp_addextendedproperty @name=N'Evententry', @value=N'申请内容' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_Evententry', 
@level2type=N'COLUMN',@level2name=N'Matter_content'

EXEC sys.sp_addextendedproperty @name=N'Evententry', @value=N'申请状态' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_Evententry', 
@level2type=N'COLUMN',@level2name=N'Matter_state'

EXEC sys.sp_addextendedproperty @name=N'Evententry', @value=N'审批人' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_Evententry', 
@level2type=N'COLUMN',@level2name=N'Matter_UpdateName'

EXEC sys.sp_addextendedproperty @name=N'Evententry', @value=N'审批时间' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_Evententry', 
@level2type=N'COLUMN',@level2name=N'Matter_UpdateDatetime'

EXEC sys.sp_addextendedproperty @name=N'Evententry', @value=N'审批人意见' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_Evententry', 
@level2type=N'COLUMN',@level2name=N'Matter_opinion'
end 
GO
--公告表
 IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_ErpShopBulletin]') AND type in (N'U'))
begin
CREATE TABLE [dbo].[tb_ErpShopBulletin](

	[ID]  [int] identity(1,1) primary key not null, 	--ID主建
	[Bulletin_issuerID]   		[varchar](26) not null,	 --发布人ID
	[Bulletin_announcement]  	[varchar](100) not null, 	 --公告类型
	[Bulletin_Title]   		[varchar](150)not null,   --标题
	[Bulletin_content]		[varchar](1000)not null,    --内容
	[Bulletin_ReleaseTime]		[Datetime] not null,   --发布时间	 
	[Bulletin_Accessory]		[varchar](200) 		--附件位置
                     )

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID主建' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ ErpBulletin', 
@level2type=N'COLUMN',@level2name=N'ID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发布人ID' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ ErpBulletin', 
@level2type=N'COLUMN',@level2name=N'Bulletin_issuerID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'公告类型' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ ErpBulletin', 
@level2type=N'COLUMN',@level2name=N'Bulletin_announcement'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标题' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ ErpBulletin', 
@level2type=N'COLUMN',@level2name=N'Bulletin_Title'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'内容' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ ErpBulletin', 
@level2type=N'COLUMN',@level2name=N'Bulletin_content'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发布时间' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ ErpBulletin', 
@level2type=N'COLUMN',@level2name=N'Bulletin_ReleaseTime'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件位置' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ ErpBulletin', 
@level2type=N'COLUMN',@level2name=N'Bulletin_Accessory'
end
GO
IF not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_ErpShopDesignatedAnnounced]') AND type in (N'U'))
begin
--指定人员查看表
CREATE TABLE [dbo].[tb_ErpShopDesignatedAnnounced](

	[ID]  [int] identity(1,1) primary key not null, 	--ID主建
	[Announced_issuerID]   		[varchar](26) not null,	 --指定人员查看公告ID
	[Announced_announcement]  	[varchar](100) not null, --公告ID
                     )

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID主建' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpDesignatedAnnounced', 
@level2type=N'COLUMN',@level2name=N'ID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'指定人员查看公告ID' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpDesignatedAnnounced', 
@level2type=N'COLUMN',@level2name=N'Announced_issuerID'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'公告ID' , 
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpDesignatedAnnounced', 
@level2type=N'COLUMN',@level2name=N'Announced_announcement'
end
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View_Shopannouncement]'))
begin
DROP VIEW [dbo].[View_Shopannouncement]
end
GO
--公告与指定人员视图
Create view View_ShopAnnouncement
AS
select a.ID,
a.Announced_issuerID,
a.Announced_announcement,
b.ID as 'Uid',
b.Bulletin_issuerID,
b.Bulletin_announcement,
b.Bulletin_Title,
b.Bulletin_content,
b.Bulletin_ReleaseTime,
b.Bulletin_Accessory from tb_ErpDesignatedAnnounced  a Left   JOIN
 tb_ErpBulletin  b on  a.Announced_announcement=b.ID 
GO